﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class PhieuNhapHangDAO
    {
        public List<PhieuNhapHangDTO> HienThi()
        {
            string sql = "select MAPN, MANCC, NGAYNHAP, cast(TONGTIEN as int) as TongTienV from PHIEUNHAP";
            SqlConnection conn = DataProvider.ConnectionDB();
            SqlCommand command = new SqlCommand(sql, conn);
            SqlDataReader reader = command.ExecuteReader();

            List<PhieuNhapHangDTO> danhsach = new List<PhieuNhapHangDTO>();
            while (reader.Read())
            {
                PhieuNhapHangDTO dto = new PhieuNhapHangDTO();
                dto.MAPN = reader.GetString(0);
                dto.MANCC = reader.GetString(1);
                dto.NGAYNHAP = reader.GetDateTime(2);
                dto.TONGTIEN = reader.GetInt32(3);
                danhsach.Add(dto);
            }
            conn.Close();
            return danhsach;
        }

        public int TongTien(string MAPN)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "Select cast(sum(SoLuong*DonGia) as int) as tongtien From CTPHIEUNHAP where MAPN=";
            sql += "'" + MAPN + "'";
            SqlCommand cmdTongTien = new SqlCommand(sql, conn);
            SqlDataReader rd = cmdTongTien.ExecuteReader();
            rd.Read();
            int kq = int.Parse(rd[0].ToString());
            conn.Close();
            return kq;
        }

        public int ThemPNH(PhieuNhapHangDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "insert into PHIEUNHAP values(";
            sql += "N'" + dto.MAPN + "',";
            sql += "N'" + dto.MANCC + "',";
            sql += "'"+dto.NGAYNHAP+"',";
            sql += dto.TONGTIEN;
            sql += ")";

            SqlCommand command = new SqlCommand(sql, conn);
            int kq = command.ExecuteNonQuery();
            conn.Close();
            if (kq <= 0)
                return 0;
            return 1;
        }

        public int CapNhat(PhieuNhapHangDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "update PHIEUNHAP ";
            sql += "set MAPN=";
            sql += "N'" + dto.MAPN + "',";
            sql += "MANCC=";
            sql += "N'" + dto.MANCC + "',";
            sql += "NGAYNHAP=";
            sql += "'"+dto.NGAYNHAP+"'";
            sql += ",";
            sql += "TONGTIEN=";
            sql += dto.TONGTIEN;
            sql += " where MAPN=";
            sql += "'" + dto.MAPN + "'";

            SqlCommand command = new SqlCommand(sql, conn);
            int kq = command.ExecuteNonQuery();
            conn.Close();
            if (kq <= 0)
                return 0;
            return 1;
        }

        public int KiemTraMAPN(string mapn)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "Select count(*) from PHIEUNHAP where MAPN=";
            sql += "'" + mapn + "'";
            SqlCommand command = new SqlCommand(sql, conn);
            int kq = (int)command.ExecuteScalar();
            conn.Close();
            if (kq <= 0)
                return 0;
            return 1;
        }
    }
}
